package NxtNmbr_DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;

public class NxtNmbrDB implements NxtNmbrDAO
  {
   public NxtNmbrDB ()
	 {
	 }

   private Connection connect ()
     {
	  Connection con = null;
	  try
	    {
   	     Class.forName("com.mysql.jdbc.Driver");
		 String url      = "jdbc:mysql://localhost:3306/m_v_a";
		 String username = "root";
		 String password = "mysqlpswd";
		 con             = DriverManager.getConnection(url, username, password);
	    }
	  catch(ClassNotFoundException e)
	    {
		 System.err.println("Database driver not found.");
	    }
	  catch(SQLException e)
	    {
		 System.err.println("Error connecting to the database: " + e);
	    }
	  return con; 
     }

   private void disconnect (Connection con)
     {
	  try
	    {
		 con.close(); 
	    }
	  catch(SQLException e)
	    {
		 System.err.println("Error disconnecting from the database: " + e);
	    }
     }
   
   public ArrayList<NxtNmbr> getNxtNmbrs ()
	 {
	  try
		{
	     Connection conn        = connect();
		 ArrayList<NxtNmbr> nns = new ArrayList<NxtNmbr>();
		 String query = "SELECT Sys, "        +
		                       "Desc01, Desc02, Desc03, Desc04, " +
		                       "Desc05, Desc06, Desc07, Desc08, " +
		                       "Desc09, Desc10, Desc11, Desc12, " +
		                       "Desc13, Desc14, Desc15, Desc16, " +
		                       "NN01,   NN02,   NN03,   NN04, "   +
		                       "NN05,   NN06,   NN07,   NN08, "   +
		                       "NN09,   NN10,   NN11,   NN12, "   +
		                       "NN13,   NN14,   NN15,   NN16  "   +
			 			  "FROM nxt_nmbr "                        +
					     "ORDER BY Sys  ASC";
		 PreparedStatement ps = conn.prepareStatement(query);
		 ResultSet         rs = ps.executeQuery();
		 while(rs.next())
		   {
		    String Sys    = rs.getString("Sys");
		    String Desc01 = rs.getString("Desc01");
		    String Desc02 = rs.getString("Desc02");
		    String Desc03 = rs.getString("Desc03");
		    String Desc04 = rs.getString("Desc04");
		    String Desc05 = rs.getString("Desc05");
		    String Desc06 = rs.getString("Desc06");
		    String Desc07 = rs.getString("Desc07");
		    String Desc08 = rs.getString("Desc08");
		    String Desc09 = rs.getString("Desc09");
		    String Desc10 = rs.getString("Desc10");
		    String Desc11 = rs.getString("Desc11");
		    String Desc12 = rs.getString("Desc12");
		    String Desc13 = rs.getString("Desc13");
		    String Desc14 = rs.getString("Desc14");
		    String Desc15 = rs.getString("Desc15");
		    String Desc16 = rs.getString("Desc16");
		    int    NN01   = rs.getInt("NN01");
		    int    NN02   = rs.getInt("NN02");
		    int    NN03   = rs.getInt("NN03");
		    int    NN04   = rs.getInt("NN04");
		    int    NN05   = rs.getInt("NN05");
		    int    NN06   = rs.getInt("NN06");
		    int    NN07   = rs.getInt("NN07");
		    int    NN08   = rs.getInt("NN08");
		    int    NN09   = rs.getInt("NN09");
		    int    NN10   = rs.getInt("NN10");
		    int    NN11   = rs.getInt("NN11");
		    int    NN12   = rs.getInt("NN12");
		    int    NN13   = rs.getInt("NN13");
		    int    NN14   = rs.getInt("NN14");
		    int    NN15   = rs.getInt("NN15");
		    int    NN16   = rs.getInt("NN16");
		    NxtNmbr nn = new NxtNmbr(Sys, Desc01, Desc02, Desc03, Desc04,
                                          Desc05, Desc06, Desc07, Desc08,
                                          Desc09, Desc10, Desc11, Desc12,
                                          Desc13, Desc14, Desc15, Desc16,
                                          NN01,   NN02,   NN03,   NN04,
                                          NN05,   NN06,   NN07,   NN08,
                                          NN09,   NN10,   NN11,   NN12,
                                          NN13,   NN14,   NN15,   NN16);
		    nns.add(nn);
		   }
		 rs.close();
		 ps.close();
		 disconnect(conn);
		 return nns;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return null;
		}
	 }

   public NxtNmbr getNxtNmbr (String Sys)
	 {
	  String tmpSys  = Sys.toUpperCase();
	  try
		{
	     Connection conn   = connect();
	     String selNxtNmbr = "SELECT Sys, "                            +
                                    "Desc01, Desc02, Desc03, Desc04, " +
                                    "Desc05, Desc06, Desc07, Desc08, " +
                                    "Desc09, Desc10, Desc11, Desc12, " +
                                    "Desc13, Desc14, Desc15, Desc16, " +
                                    "NN01,   NN02,   NN03,   NN04, "   +
                                    "NN05,   NN06,   NN07,   NN08, "   +
                                    "NN09,   NN10,   NN11,   NN12, "   +
                                    "NN13,   NN14,   NN15,   NN16  "   +
	                           "FROM nxt_nmbr "                        +
			                  "WHERE Sys  = ? "                        +
                              "ORDER BY Sys  ASC";
	     PreparedStatement ps = conn.prepareStatement(selNxtNmbr);
		 ps.setString(1, tmpSys);
		 ResultSet rs = ps.executeQuery();
		 if (rs.next())
		   {
		    String Desc01 = rs.getString("Desc01");
		    String Desc02 = rs.getString("Desc02");
		    String Desc03 = rs.getString("Desc03");
		    String Desc04 = rs.getString("Desc04");
		    String Desc05 = rs.getString("Desc05");
		    String Desc06 = rs.getString("Desc06");
		    String Desc07 = rs.getString("Desc07");
		    String Desc08 = rs.getString("Desc08");
		    String Desc09 = rs.getString("Desc09");
		    String Desc10 = rs.getString("Desc10");
		    String Desc11 = rs.getString("Desc11");
		    String Desc12 = rs.getString("Desc12");
		    String Desc13 = rs.getString("Desc13");
		    String Desc14 = rs.getString("Desc14");
		    String Desc15 = rs.getString("Desc15");
		    String Desc16 = rs.getString("Desc16");
	        int    NN01   = rs.getInt("NN01");
	        int    NN02   = rs.getInt("NN02");
	        int    NN03   = rs.getInt("NN03");
	        int    NN04   = rs.getInt("NN04");
	        int    NN05   = rs.getInt("NN05");
	        int    NN06   = rs.getInt("NN06");
	        int    NN07   = rs.getInt("NN07");
	        int    NN08   = rs.getInt("NN08");
	        int    NN09   = rs.getInt("NN09");
	        int    NN10   = rs.getInt("NN10");
	        int    NN11   = rs.getInt("NN11");
	        int    NN12   = rs.getInt("NN12");
	        int    NN13   = rs.getInt("NN13");
	        int    NN14   = rs.getInt("NN14");
	        int    NN15   = rs.getInt("NN15");
	        int    NN16   = rs.getInt("NN16");
		    NxtNmbr nn = new NxtNmbr(Sys, Desc01, Desc02, Desc03, Desc04,
                                          Desc05, Desc06, Desc07, Desc08,
                                          Desc09, Desc10, Desc11, Desc12,
                                          Desc13, Desc14, Desc15, Desc16,
                                          NN01,   NN02,   NN03,   NN04,
                                          NN05,   NN06,   NN07,   NN08,
                                          NN09,   NN10,   NN11,   NN12,
                                          NN13,   NN14,   NN15,   NN16);

		    rs.close();
		    ps.close();
		    disconnect(conn);
		    return nn;
		   }
		 else
		   return null;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	 }

   public boolean addNxtNmbr (NxtNmbr nn)
	 {
	  GregorianCalendar now = new GregorianCalendar();
	  Date today            = now.getTime(); 
	  long nanos            = today.getTime();
	  Timestamp ts          = new Timestamp(nanos);
	  try
		{
	     Connection conn = connect();
		 String insert   = "INSERT "                                         +
		                     "INTO cd_val (Sys, "                            +
		                                  "Desc01, Desc02, Desc03, Desc04, " +
		                                  "Desc05, Desc06, Desc07, Desc08, " +
		                                  "Desc09, Desc10, Desc11, Desc12, " +
		                                  "Desc13, Desc14, Desc15, Desc16, " +
		                                  "NN01,   NN02,   NN03,   NN04, "   +
		                                  "NN05,   NN06,   NN07,   NN08, "   +
		                                  "NN09,   NN10,   NN11,   NN12, "   +
		                                  "NN13,   NN14,   NN15,   NN16, "   +
		                                  "TMS_Updt, "                       +
		                                  "User_Updt, "                      +
		                                  "TMS_Crtd, "                       +
		                                  "User_Crtd) "                      +
				           "VALUES (?, " +
				                   "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
				                   "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
				                   "?, ?, ?, ?)";
		 PreparedStatement ps = conn.prepareStatement(insert);
		 ps.setString(1, nn.getSys());
		 ps.setString(2, nn.getDesc01());
		 ps.setString(3, nn.getDesc02());
		 ps.setString(4, nn.getDesc03());
		 ps.setString(5, nn.getDesc04());
		 ps.setString(6, nn.getDesc05());
		 ps.setString(7, nn.getDesc06());
		 ps.setString(8, nn.getDesc07());
		 ps.setString(9, nn.getDesc08());
		 ps.setString(10, nn.getDesc09());
		 ps.setString(11, nn.getDesc10());
		 ps.setString(12, nn.getDesc11());
		 ps.setString(13, nn.getDesc12());
		 ps.setString(14, nn.getDesc13());
		 ps.setString(15, nn.getDesc14());
		 ps.setString(16, nn.getDesc15());
		 ps.setString(17, nn.getDesc16());
		 ps.setInt(18, nn.getNN01());
		 ps.setInt(19, nn.getNN02());
		 ps.setInt(20, nn.getNN03());
		 ps.setInt(21, nn.getNN04());
		 ps.setInt(22, nn.getNN05());
		 ps.setInt(23, nn.getNN06());
		 ps.setInt(24, nn.getNN07());
		 ps.setInt(25, nn.getNN08());
		 ps.setInt(26, nn.getNN09());
		 ps.setInt(27, nn.getNN10());
		 ps.setInt(28, nn.getNN11());
		 ps.setInt(29, nn.getNN12());
		 ps.setInt(30, nn.getNN13());
		 ps.setInt(31, nn.getNN14());
		 ps.setInt(32, nn.getNN15());
		 ps.setInt(33, nn.getNN16());
		 ps.setTimestamp(34, ts);
		 ps.setString(35, "USER0001");
		 ps.setTimestamp(36, ts);
		 ps.setString(37, "USER9999");
		 ps.executeUpdate();
		 ps.close();
	     disconnect(conn);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }

   public boolean deleteNxtNmbr (NxtNmbr nn)
	 {
	  try
		{
		 Connection conn = connect();
		 String delete = "DELETE "          +
		                   "FROM nxt_nmbr " +
                          "WHERE Sys  = ?";
		 PreparedStatement ps = conn.prepareStatement(delete);
		 ps.setString(1, nn.getSys());
		 ps.executeUpdate();
		 ps.close();
	     disconnect(conn);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }

   public boolean updateNxtNmbr (NxtNmbr nn)
	 {
	  GregorianCalendar now = new GregorianCalendar();
	  Date today            = now.getTime(); 
	  long nanos            = today.getTime();
	  Timestamp ts          = new Timestamp(nanos);
	  try
		{
		 Connection conn = connect();
		 String update = "UPDATE nxt_nmbr "         +
		                    "SET Desc01    = ?, " +
		                        "Desc02    = ?, " +
		                        "Desc03    = ?, " +
		                        "Desc04    = ?, " +
		                        "Desc05    = ?, " +
		                        "Desc06    = ?, " +
		                        "Desc07    = ?, " +
		                        "Desc08    = ?, " +
		                        "Desc09    = ?, " +
		                        "Desc00    = ?, " +
		                        "Desc01    = ?, " +
		                        "Desc02    = ?, " +
		                        "Desc03    = ?, " +
		                        "Desc04    = ?, " +
		                        "Desc05    = ?, " +
		                        "Desc06    = ?, " +
		                        "NN01      = ?, " +
		                        "NN02      = ?, " +
		                        "NN03      = ?, " +
		                        "NN04      = ?, " +
		                        "NN05      = ?, " +
		                        "NN06      = ?, " +
		                        "NN07      = ?, " +
		                        "NN08      = ?, " +
		                        "NN09      = ?, " +
		                        "NN10      = ?, " +
		                        "NN11      = ?, " +
		                        "NN12      = ?, " +
		                        "NN13      = ?, " +
		                        "NN14      = ?, " +
		                        "NN15      = ?, " +
		                        "NN16      = ?, " +
		                        "TMS_Updt  = ?, " +
					            "User_Updt = ? "  +
                          "WHERE Sys  = ?";
		 PreparedStatement ps = conn.prepareStatement(update);
		 ps.setString(1, nn.getDesc01());
		 ps.setString(2, nn.getDesc02());
		 ps.setString(3, nn.getDesc03());
		 ps.setString(4, nn.getDesc04());
		 ps.setString(5, nn.getDesc05());
		 ps.setString(6, nn.getDesc06());
		 ps.setString(7, nn.getDesc07());
		 ps.setString(8, nn.getDesc08());
		 ps.setString(9, nn.getDesc09());
		 ps.setString(10, nn.getDesc10());
		 ps.setString(11, nn.getDesc11());
		 ps.setString(12, nn.getDesc12());
		 ps.setString(13, nn.getDesc13());
		 ps.setString(14, nn.getDesc14());
		 ps.setString(15, nn.getDesc15());
		 ps.setString(16, nn.getDesc16());
		 ps.setInt(17, nn.getNN01());
		 ps.setInt(18, nn.getNN02());
		 ps.setInt(19, nn.getNN03());
		 ps.setInt(20, nn.getNN04());
		 ps.setInt(21, nn.getNN05());
		 ps.setInt(22, nn.getNN06());
		 ps.setInt(23, nn.getNN07());
		 ps.setInt(24, nn.getNN08());
		 ps.setInt(25, nn.getNN09());
		 ps.setInt(26, nn.getNN10());
		 ps.setInt(27, nn.getNN11());
		 ps.setInt(28, nn.getNN12());
		 ps.setInt(29, nn.getNN13());
		 ps.setInt(30, nn.getNN14());
		 ps.setInt(31, nn.getNN15());
		 ps.setInt(32, nn.getNN16());
		 ps.setTimestamp(33, ts);
		 ps.setString(34, "USER8888");
		 ps.setString(35, nn.getSys());
		 ps.executeUpdate();
		 ps.close();
	     disconnect(conn);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	 }

   public boolean clearNxtNmbr (String Sys, int Which)
	 {
	  String tmpSys   = Sys.toUpperCase();
      int    tmpWhich = 0;
      if (Which < 1)
        {
         tmpWhich = 1;
        }
      else
        {
    	 if (Which > 16)
    	   {
    		tmpWhich = 16; 
    	   }
        }
	  GregorianCalendar now = new GregorianCalendar();
	  Date today            = now.getTime(); 
	  long nanos            = today.getTime();
	  Timestamp ts          = new Timestamp(nanos);
	  String colDesc        = "";
	  String colNN          = "";
      switch (tmpWhich)
        {
         case  1:
   	       colDesc = "Desc01";
   	       colNN   = "NN01";
   	       break;
         case  2:
   	       colDesc = "Desc02";
   	       colNN   = "NN02";
   	       break;
         case  3:
   	       colDesc = "Desc03";
   	       colNN   = "NN03";
   	       break;
         case  4:
   	       colDesc = "Desc04";
   	       colNN   = "NN04";
   	       break;
         case  5:
   	       colDesc = "Desc05";
   	       colNN   = "NN05";
   	       break;
         case  6:
   	       colDesc = "Desc06";
   	       colNN   = "NN06";
   	       break;
         case  7:
   	       colDesc = "Desc07";
   	       colNN   = "NN07";
   	       break;
         case  8:
   	       colDesc = "Desc08";
   	       colNN   = "NN08";
   	       break;
         case  9:
   	       colDesc = "Desc09";
   	       colNN   = "NN09";
   	       break;
         case 10:
   	       colDesc = "Desc10";
   	       colNN   = "NN10";
   	       break;
         case 11:
   	       colDesc = "Desc11";
   	       colNN   = "NN11";
   	       break;
         case 12:
   	       colDesc = "Desc12";
   	       colNN   = "NN12";
   	       break;
         case 13:
   	       colDesc = "Desc13";
   	       colNN   = "NN13";
   	       break;
         case 14:
   	       colDesc = "Desc14";
   	       colNN   = "NN14";
   	       break;
         case 15:
   	       colDesc = "Desc15";
   	       colNN   = "NN15";
   	       break;
         case 16:
   	       colDesc = "Desc16";
   	       colNN   = "NN16";
   	       break;
        }
	  try
		{
		 Connection conn = connect();
		 String update = "UPDATE nxt_nmbr "              +
		                    "SET " + colDesc + " = ?, "  +
		                             colNN   + " = ?, "  +
		                         "TMS_Updt        = ?, " +
					             "User_Updt       = ? "  +
                          "WHERE Sys  = ?";
		 PreparedStatement ps = conn.prepareStatement(update);
		 ps.setString(1, " ");
		 ps.setInt(2, 0);
		 ps.setTimestamp(3, ts);
		 ps.setString(4, "USER8888");
		 ps.setString(5, tmpSys);
		 ps.executeUpdate();
		 ps.close();
	     disconnect(conn);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	 }

   public int getNN (String Sys, int Which)
     {
	  String tmpSys         = Sys.toUpperCase();
      int    tmpWhich = 0;
      int    tmpNN    = 0;
      if (Which < 1)
        {
         tmpWhich = 1;
        }
      else
        {
    	 if (Which > 16)
    	   {
    		tmpWhich = 16; 
    	   }
        }
	  GregorianCalendar now = new GregorianCalendar();
	  Date today            = now.getTime(); 
	  long nanos            = today.getTime();
	  Timestamp ts          = new Timestamp(nanos);
	  String colNN          = "";
	  int    valNN          = 0;
      switch (tmpWhich)
        {
         case  1:
   	       colNN   = "NN01";
 	       break;
         case  2:
 	       colNN   = "NN02";
 	       break;
         case  3:
 	       colNN   = "NN03";
 	       break;
         case  4:
 	       colNN   = "NN04";
 	       break;
         case  5:
 	       colNN   = "NN05";
 	       break;
         case  6:
 	       colNN   = "NN06";
 	       break;
         case  7:
 	       colNN   = "NN07";
 	       break;
         case  8:
 	       colNN   = "NN08";
 	       break;
         case  9:
 	       colNN   = "NN09";
 	       break;
         case 10:
 	       colNN   = "NN10";
 	       break;
         case 11:
 	       colNN   = "NN11";
 	       break;
         case 12:
 	       colNN   = "NN12";
 	       break;
         case 13:
 	       colNN   = "NN13";
 	       break;
         case 14:
 	       colNN   = "NN14";
 	       break;
         case 15:
 	       colNN   = "NN15";
 	       break;
         case 16:
 	       colNN   = "NN16";
 	       break;
        }
	  try
		{
		 Connection conn = connect();
	     String selNxtNmbr = "SELECT Sys, "         +
                                   colNN   + " "  +
                             "FROM nxt_nmbr "     +
                            "WHERE Sys  = ? "     +
                            "ORDER BY Sys ASC";
		 PreparedStatement ps = conn.prepareStatement(selNxtNmbr);
		 ps.setString(1, tmpSys);
		 ResultSet rs = ps.executeQuery();
		 if (rs.next())
		   {
	        tmpNN = rs.getInt(colNN);
		    rs.close();
		    ps.close();
		    disconnect(conn);
		   }
		 else
		   return 0;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return 0;
		}
      valNN = tmpNN + 1;
      try
        {
 		 Connection conn = connect();
	     String update = "UPDATE nxt_nmbr "           +
		                    "SET " + colNN + " = ?, " +
		                         "TMS_Updt     = ?, " +
				  	             "User_Updt    = ? "  +
                          "WHERE Sys  = ? "           +
				  	        "AND " + colNN + " = ?";
		 PreparedStatement ps = conn.prepareStatement(update);
		 ps.setInt(1, valNN);
		 ps.setTimestamp(2, ts);
		 ps.setString(3, "USER8888");
		 ps.setString(4, tmpSys);
		 ps.setInt(5, tmpNN);
		 ps.executeUpdate();
		 ps.close();
	     disconnect(conn);
		 return valNN;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return 0;
		}
     }
  }
